package com.example.swaggerDemo.service;

import com.example.swaggerDemo.Dao.SalaryDao;
import com.example.swaggerDemo.entity.SalaryEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.RequestParam;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@Repository
public class SalaryService implements SalaryDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 按用户微信openid到账类型查询财务数据
     * @param openid
     * @param status
     * @return
     */
    @Override
    public List<SalaryEntity> findOpenid(@RequestParam("openid") String openid, @RequestParam("status") String status,
                                         @RequestParam("pageindex") int pagindex,
                                         @RequestParam("pagesize") int pagesize)
    {
        String sql = "select * from salary WHERE  openid= '" + openid + "'" + " AND status=" + "'" + status + "'"
                + " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        List<SalaryEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(SalaryEntity.class));
        return dto;
    }

    /**
     * 按订单id到账类型查询财务数据
     * @param id
     * @param status
     * @return
     */
    @Override
    public SalaryEntity findid(@RequestParam("id") String id, @RequestParam("status") String status)
    {
        String sql = "select * from salary WHERE  id= '" + id + "'" + " AND status=" + "'" + status + "'";
        SalaryEntity dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<SalaryEntity>(SalaryEntity.class));
        return dto;
    }


    /**
     * 通过上级id修改订单状态用于支付修改
     * @param status 状态:状态:状态:0=未申请,1=待付款,2=已付款,3=已处理不显示
     * @param id
     * @return
     */
    @Override
    public int updateOrderTypeUpid(@RequestParam("id") String id, @RequestParam("status") Integer status,@RequestParam("upid") Integer upid,@RequestParam("userimg") String userimg) {
        String sql = "update  salary set status=?,upid=?,userimg=? where id=?";
        return jdbcTemplate.update(sql,status,upid,userimg,id);
    }

    /**
     * 修改佣金状态
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateOrderType(@RequestParam("id") String id, @RequestParam("status") Integer status) {
        String sql = "update  salary set status=? where id=?";
        return jdbcTemplate.update(sql,status,id);
    }

    /**
     * 添加普通财务数据
     * @param openid
     * @param
     * @return
     */
    @Override
    public int insert(@RequestParam("openid") String openid, @RequestParam("money") String money) {
        Integer createtime= Math.toIntExact(System.currentTimeMillis() / 1000);
        String status="0";
        String type="2";
        String sql = "insert into salary(openid,money,createtime,status,type) " +
                "values(?,?,?,?,?)";
        return jdbcTemplate.update(sql,openid,money,createtime,status,type);
    }


    /**
     * 添加提现申请
     * @param openid
     * @param
     * @return
     */
    @Override
    public int insertTx(@RequestParam("openid") String openid,
                        @RequestParam("money") String money,
                        @RequestParam("kaihuhang") String kaihuhang,
                        @RequestParam("yinhangka") String yinhangka,
                        @RequestParam("remake") String remake,
                        @RequestParam("nickname") String nickname,
                        @RequestParam("userimg") String userimg
    ) {

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String createtime = df.format(new Date());// new Date()为获取当前系统时间，也可使用当前时间戳
        String status="1";
        String type="3";
        String sql = "insert into salary(openid,money,createtime,status,type,kaihuhang,yinhangka,remake,nickname,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, openid);
            ps.setString(2, money);
            ps.setString(3, createtime);
            ps.setString(4, status);
            ps.setString(5, type);
            ps.setString(6, kaihuhang);
            ps.setString(7,yinhangka);
            ps.setString(8,remake);
            ps.setString(9, nickname);
            ps.setString(10, userimg);
            return  ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 添加佣金记录
     * @param openid
     * @param
     * @return
     */
    @Override
    public int insert(@RequestParam("openid") String openid, @RequestParam("money") String money,@RequestParam("orderid") String orderid, @RequestParam("type") String type, @RequestParam("nickname") String nickname) {

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        String createtime = df.format(new Date());// new Date()为获取当前系统时间，也可使用当前时间戳
        String status="0";
        String sql = "insert into salary(openid,money,createtime,type,nickname,status,orderid) " +
                "values(?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        String money2 =(String.format("%.1f",Double.parseDouble(money)));
        jdbcTemplate.update(connection -> {
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, openid);
            ps.setString(2, money2);
            ps.setString(3, createtime);
            ps.setString(4, type);
            ps.setString(5, nickname);
            ps.setString(6, status);
            ps.setString(7, orderid);
            return  ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 查询提现来源明细
     * @param upid
     * @return
     * @throws SQLException
     */
    @Override
    public  List<SalaryEntity> findByupid(@RequestParam("upid") String upid) throws SQLException {
        String sql = "select * from salary WHERE  upid= '" +  upid + "'";
            List<SalaryEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(SalaryEntity.class));
        return dto;
    }

    /**
     * 按id查询
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public  SalaryEntity findByid(@RequestParam("id") String id) throws SQLException {
        String sql = "select * from salary WHERE  id= '" +  id + "'";
        SalaryEntity dto=null;
        try {
            dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<SalaryEntity>(SalaryEntity.class));
        }catch (Exception ex)
        {
            dto=null;
        }

        return dto;
    }

    //通过用户id和订单号 查看生成的佣金
    @Override
    public   List<SalaryEntity> findByUserid(@RequestParam("orderid") String orderid) throws SQLException {
        String sql = "select * from salary WHERE  orderid="+"'"+orderid+"'";
        List<SalaryEntity> dto=null;
        try {
              dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(SalaryEntity.class));
        }catch (Exception ex)
        {
            dto=null;
        }

        return dto;
    }

    /**
     * 管理员查看提现记录
     * @param pagindex
     * @param pagesize
     * @return状态:0=未申请,1=待付款,2=已付款,3=已处理不显示
     * @throws SQLException
     */
    @Override
    public List<SalaryEntity> findadmin(@RequestParam("status") String status, @RequestParam("pageindex") int pagindex, @RequestParam("pagesize") int pagesize) throws SQLException {
        String sql = "select * from salary  WHERE  status= '" +  status + "'"+
                " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        List<SalaryEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(SalaryEntity.class));
        return dto;
    }


}
